/*
 * Copyright (c) 2017,安徽皖通科技股份有限公司 All rights reserved.
 */

package com.wttech.dzzf.process.dao;

		import com.wttech.dzzf.process.model.error.TelMissing;
		import org.apache.commons.lang.StringUtils;
		import org.springframework.jdbc.core.BeanPropertyRowMapper;
		import org.springframework.jdbc.core.JdbcTemplate;
		import org.springframework.stereotype.Repository;

		import javax.annotation.Resource;
		import java.util.List;

/**
 * 生成错误数据信息
 *
 * @author MaShibo
 * @date 2017/12/1
 */
@Repository
public class GeneratorErrorDataDao {

	private static final String DEFAULT_ROWNUM_QUERY =
			"SELECT * " +
					"  FROM (SELECT M.*, ROWNUM AS RM " +
					"          FROM (%s) M ) " +
					" WHERE RM >= ? " +
					"   AND RM <= ?";

	private static final String DEFAULT_COUNT_QUERY = "SELECT COUNT(1) FROM (%s)";

	/**
	 * 中行TopClass
	 */
	private static final int TOPCLASS_ZH = 6109;

	/**
	 * 缺失手机号查询SQL
	 */
	private static final String PHONENO_MISSING =
			"WITH S AS " +
					" (SELECT * " +
					"    FROM (SELECT A.CARDNETID || A.ECARDNO AS CARDNO, " +
					"                 T.FUNDACCOUNTID, " +
					"                 a.phoneno, " +
					"                 ROW_NUMBER() OVER(PARTITION BY T.FUNDACCOUNTID ORDER BY A.OPTON DESC) AS RM " +
					"            FROM AC_CARDINFO A " +
					"            JOIN AC_ACCOUNT T " +
					"              ON A.ACCOUNTID = T.ACCOUNTID " +
					"           WHERE A.ECARDSTATUS IN (1, 2, 3)) " +
					"   WHERE RM = 1) " +
					"SELECT S.CARDNO ECARDNO, " +
					"       F.FUNDACCOUNTNAME FUNDACCOUNTNAME, " +
					"       DECODE(F.CERTIFITYPE, " +
					"              0, " +
					"              '身份证', " +
					"              1, " +
					"              '军官证', " +
					"              2, " +
					"              '护照', " +
					"              3, " +
					"              '入境证', " +
					"              4, " +
					"              '临时身份证', " +
					"              5, " +
					"              '武警警察身份证', " +
					"              6, " +
					"              '台胞证', " +
					"              7, " +
					"              '港澳通行证', " +
					"              101, " +
					"              '组织机构代码证', " +
					"              102, " +
					"              '统一社会信用代码证', " +
					"              '错误证件类型') CERTIFITYPE, " +
					"       F.CERTIFINO CERTIFINO, " +
					"       NVL(F.PHONE, F.MOBILE) MOBILE, " +
					"       TO_CHAR(F.OPTON, 'yyyy-MM-dd HH24:mi:ss') OPTON  " +
					"  FROM AC_FUNDACCOUNT F " +
					"  JOIN S " +
					"    ON F.FUNDACCOUNTID = S.FUNDACCOUNTID " +
					"  LEFT JOIN XXJH_TOPCLASSISSUERAGENT X " +
					"    ON F.TOPCLASS = X.TOPCLASS " +
					" WHERE F.ACCOUNTSTATUS = 1 " +
					"   AND F.TOPCLASS = ? " +
					"   AND ((F.MOBILE IS NULL AND F.PHONE IS NULL AND s.phoneno IS NULL) OR " +
					"       (F.MOBILE IS NOT NULL AND NOT REGEXP_LIKE(F.MOBILE, '^1[3|4|5|7|8|9][0-9]{9}$')) OR " +
					"       (F.PHONE IS NOT NULL AND NOT REGEXP_LIKE(F.PHONE, '^1[3|4|5|7|8|9][0-9]{9}$')) OR " +
					"       (s.phoneno IS NOT NULL AND NOT regexp_like(s.phoneno, '^1[3|4|5|7|8|9][0-9]{9}$')))";


	@Resource(name = "dzzfJdbcTemplate")
	private JdbcTemplate jdbcTemplate;

	public int queryTelMissingNum() {
		return jdbcTemplate.queryForObject(String.format(DEFAULT_COUNT_QUERY, PHONENO_MISSING), Integer.class, TOPCLASS_ZH);
	}

	public int queryZHCardNum(){
		return jdbcTemplate.queryForObject("SELECT COUNT(1) FROM AC_CARDINFO A WHERE A.TOPCLASS=6109 AND A.ECARDSTATUS IN (1, 2, 3)", Integer.class);
	}


	public List<TelMissing> queryTelMissingInfos(int startRow, int endRow) {
		final String sql = String.format(DEFAULT_ROWNUM_QUERY, PHONENO_MISSING);
		return jdbcTemplate.query(sql, BeanPropertyRowMapper.newInstance(TelMissing.class), TOPCLASS_ZH, startRow, endRow);
	}

	public List<String> queryZHAllCardNos(int startRow, int endRow){
		final String sql = String.format(StringUtils.replaceOnce(DEFAULT_ROWNUM_QUERY, "*", " CARDNO "),
				"SELECT A.CARDNETID || A.ECARDNO AS CARDNO  " +
						"  FROM AC_CARDINFO A  " +
						" WHERE A.TOPCLASS = 6109  " +
						"   AND A.ECARDSTATUS IN (1, 2, 3)");
		return jdbcTemplate.queryForList(sql, String.class, startRow, endRow);
	}

}
